package spectra.ee.web.proposal.service;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import com.ibatis.sqlmap.client.SqlMapClient;

import spectra.base.log.Logger;
import spectra.base.sql.ibatis.SqlMapperWeb;
import spectra.base.util.DateUtil;
import spectra.base.util.FileUtil;
import spectra.base.util.StringUtil;
import spectra.ee.web.WebPublic;
import spectra.ee.web.mileage.model.MileageDetail;
import spectra.ee.web.mileage.model.MileageScore;
import spectra.ee.web.mileage.service.MileageManager;
import spectra.ee.web.proposal.model.Proposal;
import spectra.ee.web.proposal.model.ProposalAttach;
import spectra.ee.web.proposal.model.ProposalContents;

/**
 * 노드 관련 클래스.
 *
 * @author kmhan
 */
public class ProposalServiceBase
{

    /** Logger객체. */
    private static Logger logger = Logger.getLoggerInstance(ProposalServiceBase.class.getName());

    /** SqlMapClient 객체. */
    protected static SqlMapClient sqlMap = SqlMapperWeb.getSqlMapWebInstance();

    /**
     * 지식제안/신고 리스트를 가져오는 메소드이다.
     * @param strParentId
     * @return
     */
    public List selectKbProposalList(int pageNo, int rowsPerPage, String proposalType, String domainId, String condition, String searchText, String approvalStatus, String createdBy)
    {
        List list = null;
        try
        {
        	Proposal obj = new Proposal();
        	obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
        	obj.setProposalType(proposalType);
        	obj.setDomainId(domainId);
        	obj.setStartNo((pageNo-1) * rowsPerPage + 1);
       	 	obj.setEndNo(pageNo * rowsPerPage);
       	 	obj.setCondition(condition);
       	 	obj.setRowsPerPage(rowsPerPage);
    	 	if (searchText != null)
    	 	{
	 	   	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
	 		 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
    	 	}
    	 	obj.setSearchText(searchText);
    	 	obj.setApprovalStatus(approvalStatus);
    	 	obj.setCreatedBy(createdBy);
 	 	
            list = (List)sqlMap.queryForList("Proposal.selectProposalList", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * 지식제안/신고 리스트의 개수를 가져오는 메소드이다.
     * @param serviceType
     * @param proposalType
     * @param domainId
     * @return
     */
    public int selectKbProposalListCount(String proposalType, String domainId, String condition, String searchText, String approvalStatus, String createdBy)
    {   	 
   	 	int iResult = 0;
   	 	Proposal obj = new Proposal();
   	 	obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
   	 	obj.setProposalType(proposalType);
   	 	obj.setDomainId(domainId); 	
   	 	obj.setCondition(condition);
	 	if (searchText != null)
	 	{
	   	 	searchText = searchText.replaceAll("_", "\\\\_"); // 쿼리 escape지정
		 	searchText = searchText.replaceAll("%", "\\\\%"); // 쿼리 escape지정
	 	}
	 	obj.setSearchText(searchText);
	 	obj.setApprovalStatus(approvalStatus);
	 	obj.setCreatedBy(createdBy);
	 	
        try
        {
        	iResult = ((Integer)sqlMap.queryForObject("Proposal.selectProposalListCount", obj)).intValue();
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return iResult;
    } 

    /**
     * 제안/신고 아이디로 제안/신고 내용 가져오는 메소드이다.
     * @param proposalId
     * @return
     */
    public Proposal selectProposalByProposalId(String proposalId)
    {
    	Proposal proposalObj = null;
    	try
        {
    		proposalObj = (Proposal) sqlMap.queryForObject("Proposal.selectProposalByProposalId", proposalId);
    		if (proposalObj != null)
    		{
    			// 지식/제안 내용 가져오기
    			List proposalContentsList = selectProposalContentsByProposalId(proposalId);
          		
   		 		String contentsQuestion = "";
   		 		String contentsAnswer = "";
   		 		for (Iterator itContents = proposalContentsList.iterator(); itContents.hasNext();)
   		 		{
   		 			ProposalContents proposalContentsObj = (ProposalContents) itContents.next();
   		 			if (proposalId.equals(proposalContentsObj.getProposalId()))
   		 			{
   		 				if (WebPublic.CONTENTS_TYPE_QUESTION.equals(proposalContentsObj.getContentsType()))
   		 					contentsQuestion += StringUtil.nvl(proposalContentsObj.getContents(), "");
   		 				else if (WebPublic.CONTENTS_TYPE_ANSWER.equals(proposalContentsObj.getContentsType()))
   		 					contentsAnswer += StringUtil.nvl(proposalContentsObj.getContents(), "");
   		 			}
   		 		}
   		 		proposalObj.setContentsQuestion(contentsQuestion);
   		 		proposalObj.setContentsAnswer(contentsAnswer);
   		 	
   		 		// 첨부파일 정보 가져오기
   		 		List proposalAttachList = selectProposalAttachByProposalId(proposalId);
   		 		if (proposalAttachList != null)
   		 			proposalObj.setAttachCount(proposalAttachList.size());
   		 		proposalObj.setAttachList(proposalAttachList);
    		}
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return proposalObj;
    }
    
    /**
     * 제안/신고 아이디로 조회수를 업데이트 하는 메소드이다.
     * @param proposalId
     * @return
     */
    public boolean updateProposalHitCount(String proposalId)
    {
        boolean result = false;        
        try
        {
            sqlMap.update("Proposal.updateProposalHitCount", proposalId);
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }         
        return result;
    }
    
    /**
     * 지식아이디로 지식신고 리스트 가져오는 메소드
     * @param kbId
     * @return
     */
    public List selectKbProposalListByKbId(String proposalType, String kbId)
    {
   	 	List list = null;
        try
        {
       	 	Proposal obj = new Proposal();
       	 	obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
       	 	obj.setProposalType(proposalType);
       	 	obj.setTargetId(kbId);
            list = (List)sqlMap.queryForList("Proposal.selectProposalListByTargetId", obj);
            
            if (list != null)
            {
       		 	List proposalContentsList = selectProposalContentsByKbId(WebPublic.SERVICE_TYPE_KB, WebPublic.PROPOSAL_TYPE_ERROR, kbId);
       		 
       		 	for (Iterator it = list.iterator(); it.hasNext();)
       		 	{
       		 		Proposal proposalObj = (Proposal)it.next();
       		 		String proposalId = proposalObj.getProposalId();
       			 
       		 		String contentsQuestion = "";
       		 		String contentsAnswer = "";
       		 		for (Iterator itContents = proposalContentsList.iterator(); itContents.hasNext();)
       		 		{
       		 			ProposalContents proposalContentsObj = (ProposalContents) itContents.next();
       		 			if (proposalId.equals(proposalContentsObj.getProposalId()))
       		 			{
       		 				if (WebPublic.CONTENTS_TYPE_QUESTION.equals(proposalContentsObj.getContentsType()))
       		 					contentsQuestion += StringUtil.nvl(proposalContentsObj.getContents(), "");
       		 				else if (WebPublic.CONTENTS_TYPE_ANSWER.equals(proposalContentsObj.getContentsType()))
       		 					contentsAnswer += StringUtil.nvl(proposalContentsObj.getContents(), "");
       		 			}
       		 		}
       			 
       		 		proposalObj.setContentsQuestion(contentsQuestion);
       		 		proposalObj.setContentsAnswer(contentsAnswer);
       		 	}
   		 	}
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * 지식/제안아이디로 정보를 가져오는 메소드이다.
     * @param proposalId
     * @return
     */
    public List selectProposalContentsByProposalId(String proposalId)
    {
        List list = null;
        try
        {       	 	
            list = (List)sqlMap.queryForList("Proposal.selectProposalContentsByProposalId", proposalId);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * kbId로 proposal내용을 가져오는 메소드
     * @param kbId
     * @return
     */
    public List selectProposalContentsByKbId(String serviceType, String proposalType, String kbId)
    {
        List list = null;
        try
        {
       	 	Proposal obj = new Proposal();
       	 	obj.setServiceType(serviceType);
       	 	obj.setProposalType(proposalType);
       	 	obj.setTargetId(kbId);
            list = (List)sqlMap.queryForList("Proposal.selectProposalContentsByKbId", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * 지식/제안아이디로 첨부파일 정보를 가져오는 메소드이다.
     * @param proposalId
     * @return
     */
    public List selectProposalAttachByProposalId(String proposalId)
    {
        List list = null;
        try
        {       	 	
            list = (List)sqlMap.queryForList("Proposal.selectProposalAttachByProposalId", proposalId);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return list;
    }
    
    /**
     * 지식/신고를 입력하는 메소드이다.
     * @param obj
     * @return
     */
    public boolean insertProposal(Proposal obj)
    {
        boolean result = false;
        try
        {
       	 	String currDate = DateUtil.getCurrDateTimeStamp();             
            sqlMap.startTransaction();
            
       	 	String proposalId = (String) sqlMap.queryForObject("Proposal.selectNextKey", null);
       	 	obj.setProposalId(proposalId);
       	 	// t_proposal테이블에 insert
            sqlMap.insert("Proposal.insert", obj);
            
            String[] arProposalContents = StringUtil.splitContents(obj.getContentsQuestion(), WebPublic.MAX_CONTENTS_LENGTH);
            
            // 내용입력
            ProposalContents proposalContentsObj = null;
            for (int i=0; i<arProposalContents.length; i++)
            {
            	proposalContentsObj = new ProposalContents();
           	 	proposalContentsObj.setProposalId(proposalId);
           	 	proposalContentsObj.setContentsType(WebPublic.CONTENTS_TYPE_QUESTION);
           	 	proposalContentsObj.setContentsSeq(i);
           	 	proposalContentsObj.setContents(arProposalContents[i]);
           	 	proposalContentsObj.setCreatedDate(currDate);
	            // t_proposal_contents에 insert
	            sqlMap.insert("Proposal.insertProposalContents", proposalContentsObj);
            }
            
            // 첨부파일이 있다면 입력
            List attachList = obj.getAttachList();
            if (attachList != null && attachList.size() > 0)
            {
            	for (int i=0; i<attachList.size(); i++)
            	{
            		ProposalAttach attachObj = (ProposalAttach) attachList.get(i);
            		attachObj.setProposalId(proposalId);
            		attachObj.setAttachSeq(i);
            		attachObj.setAttachExt(FileUtil.getFileExt(attachObj.getAttachName()));
            		attachObj.setCreatedBy(obj.getCreatedBy());
            		attachObj.setCreatedDate(currDate);
            		sqlMap.insert("Proposal.insertProposalAttach", attachObj);
            	}
            }
            
            // 제안에 대한 마일리지를 등록한다.
            MileageManager mileageManager = new MileageManager();
            String mileageType = WebPublic.MILEAGE_FLAG_PROREC; // 지식제안 등록
            if (WebPublic.PROPOSAL_TYPE_ERROR.equals(obj.getProposalType()))
            	mileageType = WebPublic.MILEAGE_FLAG_ERRREC; // 지식신고 등록
            // 해당 기간내의 마일리지 점수를 가져온다.
            MileageDetail mileageDetailObj = mileageManager.selectMileageDetailByMileageType(obj.getDomainId(), currDate.substring(0, 6), mileageType);
            if (mileageDetailObj != null) // 
            {
            	MileageScore mileageScore = new MileageScore();
            	mileageScore.setKmId(obj.getProposalId());
            	mileageScore.setSeq(0);
            	mileageScore.setMileageType(mileageType);
            	mileageScore.setScore(mileageDetailObj.getApplyMileage());
            	mileageScore.setCreatedDate(currDate);
            	sqlMap.insert("Mileage.insertMileageScore", mileageScore);
            }
            sqlMap.commitTransaction();
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }   
        finally
        {
            try
            {
                sqlMap.endTransaction();
            }
            catch(SQLException e)
            {
                logger.error(e.getMessage());
            }
        }
        return result;
    }
    
    /**
     * 제안/신고를 수정하는 메소드
     * @param obj
     * @return
     */
    public boolean updateProposal(Proposal obj)
    {
        boolean result = false;
        try
        {
        	String currDate = DateUtil.getCurrDateTimeStamp();             
            sqlMap.startTransaction();
                    	 
            sqlMap.update("Proposal.updateProposal", obj);
            
            // t_proposal_contents 삭제
            ProposalContents proposalContents = new ProposalContents();
            proposalContents.setProposalId(obj.getProposalId());
            proposalContents.setContentsType(WebPublic.CONTENTS_TYPE_QUESTION);
            sqlMap.delete("Proposal.deleteProposalByProposalIdContentsType", proposalContents);
            
            String[] arProposalContents = StringUtil.splitContents(obj.getContentsQuestion(), WebPublic.MAX_CONTENTS_LENGTH);
            
            ProposalContents proposalContentsObj = null;
            for (int i=0; i<arProposalContents.length; i++)
            {
           	 	proposalContentsObj = new ProposalContents();
           	 	proposalContentsObj.setProposalId(obj.getProposalId());
           	 	proposalContentsObj.setContentsType(WebPublic.CONTENTS_TYPE_QUESTION);
           	 	proposalContentsObj.setContentsSeq(i);
           	 	proposalContentsObj.setContents(arProposalContents[i]);
           	 	proposalContentsObj.setCreatedDate(currDate);
	             // t_proposal_contents에 insert
	            sqlMap.insert("Proposal.insertProposalContents", proposalContentsObj);
            }
            
            // 첨부파일 삭제 후 재입력
            sqlMap.delete("Proposal.deleteProposalAttachByProposalId", obj.getProposalId());
            
            // 첨부파일이 있다면 입력
            List attachList = obj.getAttachList();
            if (attachList != null && attachList.size() > 0)
            {
            	for (int i=0; i<attachList.size(); i++)
            	{
            		ProposalAttach attachObj = (ProposalAttach) attachList.get(i);
            		attachObj.setProposalId(obj.getProposalId());
            		attachObj.setAttachSeq(i);
            		attachObj.setAttachExt(FileUtil.getFileExt(attachObj.getAttachName()));
            		attachObj.setCreatedBy(obj.getCreatedBy());
            		attachObj.setCreatedDate(currDate);
            		sqlMap.insert("Proposal.insertProposalAttach", attachObj);
            	}
            }
            
            sqlMap.commitTransaction();
            result = true;
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }   
        finally
        {
            try
            {
                sqlMap.endTransaction();
            }
            catch(SQLException e)
            {
                logger.error(e.getMessage());
            }
        }
        return result;
    }
    
    /**
     * 지식/제안을 삭제하는 메소드
     * @param obj
     * @return
     */
    public boolean deleteProposal(Proposal obj)
    {
        boolean result = false;
        try
        {
        	Proposal proposalObj = selectProposalByProposalId(obj.getProposalId());
        	// 등록한 계정과 수정한 계정이 같을 경우에만 삭제할 수 있다.
        	if (proposalObj.getCreatedBy().equals(obj.getUpdatedBy()))
        	{
	       	 	String currDate = DateUtil.getCurrDateTimeStamp();            
	            sqlMap.update("Proposal.updateProposal", obj);             
	            
	            result = true;
        	}
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return result;
    }
    
    /**
     * 지식/신고의 이전글을 가져오는 메소드이다.
     * @param proposalType
     * @param domainId
     * @param proposalId
     * @param createdDate
     * @return
     */
    public Proposal selectPrevKbProposal(String proposalType, String domainId, String proposalId, String createdDate, String createdBy)
    {
    	Proposal proposalObj = null;
    	try
        {
    		Proposal obj = new Proposal();
    		obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
    		obj.setProposalType(proposalType);
    		obj.setDomainId(domainId);
    		obj.setProposalId(proposalId);
    		obj.setCreatedDate(createdDate);
    		obj.setCreatedBy(createdBy);
    		proposalObj = (Proposal) sqlMap.queryForObject("Proposal.selectPrevKbProposal", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return proposalObj;
    }
    
    /**
     * 지식/신고의 다음글 가져오는 메소드이다.
     * @param proposalType
     * @param domainId
     * @param proposalId
     * @param createdDate
     * @return
     */
    public Proposal selectNextKbProposal(String proposalType, String domainId, String proposalId, String createdDate, String createdBy)
    {
    	Proposal proposalObj = null;
    	try
        {
    		Proposal obj = new Proposal();
    		obj.setServiceType(WebPublic.SERVICE_TYPE_KB);
    		obj.setProposalType(proposalType);
    		obj.setDomainId(domainId);
    		obj.setProposalId(proposalId);
    		obj.setCreatedDate(createdDate);
    		obj.setCreatedBy(createdBy);
    		proposalObj = (Proposal) sqlMap.queryForObject("Proposal.selectNextKbProposal", obj);
        }
        catch (SQLException e)
        {
            logger.error(e.getMessage());
        }
        return proposalObj;
    }
}
